package db.jdbc;

import db.MembershipApplicationRepository;
import domain.Club;
import domain.MembershipApplication;
import domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 社团成员申请资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcMembershipApplicationRepository implements MembershipApplicationRepository {
    private static final String INSERT_MembershipApplication = "insert into membershipApplication(requestedClubID,applicationTime,status,description) values(?,current_timestamp,?,?)";
    private static final String INSERT_applicant_membershipApplication = "insert into applicant_membershipApplication(userID,membershipApplicationID) values(?,?)";
    private static final String DELETE_MembershipApplication = "delete from membershipApplication where membershipApplicationID=?";
    private static final String DELETE_applicant_membershipApplication = "delete from applicant_membershipApplication where membershipApplicationID=?";
    private static final String UPDATE_MembershipApplication = "update membershipApplication set requestedClubID=?,status=?,description=? where membershipApplicationID=?";
    private static final String SELECT_MembershipApplication = "select * from membershipApplication";
    private static final String SELECT_MembershipApplication_Pagination = SELECT_MembershipApplication + " order by membershipApplicationID  limit ? offset  ?";

    private static JdbcTemplate jdbc;

    @Autowired
    public JdbcMembershipApplicationRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取申请的总数
     *
     * @return 申请的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(membershipApplicationID) from membershipApplication");
    }

    /**
     * 获取社团成员的申请
     *
     * @param club 社团
     * @return 申请的数量
     */
    @Override
    public long countByClub(Club club) {
        return jdbc.queryForInt("select count(membershipApplicationID) from membershipApplication where requestedClubID=?", club.getClubID());
    }

    /**
     * 创建一个新的申请
     *
     * @param membershipApplication 新申请的信息
     * @return 新创建的申请
     */
    @Override
    public MembershipApplication save(MembershipApplication membershipApplication) {
        Date tmpTime = new Date();
        int id = jdbc.queryForInt(INSERT_MembershipApplication +" returning membershipapplicationID", membershipApplication.getRequestedClubID(), membershipApplication.getStatus(), membershipApplication.getDescription());

        jdbc.update(INSERT_applicant_membershipApplication, ((User) membershipApplication.getApplicant()).getUserID(), id);
        return membershipApplication;
    }

    /**
     * 根据ID查找申请
     *
     * @param membershipApplicationID 申请的ID
     * @return 对应ID的申请
     */
    @Override
    public MembershipApplication findOne(int membershipApplicationID) {
        MembershipApplication membershipApplication = null;
        try {
            membershipApplication = jdbc.queryForObject(SELECT_MembershipApplication + " where membershipApplicationID=?", new MembershipApplicationRowMapper(), membershipApplicationID);
        } catch (DataAccessException e) {
        }
        return membershipApplication;
    }

    /**
     * 更新申请
     *
     * @param membershipApplication 更新后的申请信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateMembershipApplication(MembershipApplication membershipApplication) {
        int userID = jdbc.queryForInt("select userID from applicant_membershipapplication where membershipApplicationID=?", membershipApplication.getMembershipApplicationID());
        jdbc.update("insert into club_users values (?,?)", membershipApplication.getRequestedClubID(), userID);
        return jdbc.update(UPDATE_MembershipApplication,
                membershipApplication.getRequestedClubID(),
                membershipApplication.getStatus(),
                membershipApplication.getDescription(),
                membershipApplication.getMembershipApplicationID()) != 0;
    }

    /**
     * 分页查找申请
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的申请列表
     */
    @Override
    public PaginationSupport<MembershipApplication> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<MembershipApplication>(new ArrayList<MembershipApplication>(0), 0);
        }

        List<MembershipApplication> items = jdbc.query(SELECT_MembershipApplication_Pagination, new MembershipApplicationRowMapper(), pageSize, startIndex);
        return new PaginationSupport<MembershipApplication>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 删除申请
     *
     * @param membershipApplicationID 申请的ID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteMembershipApplication(int membershipApplicationID) {
        return jdbc.update(DELETE_applicant_membershipApplication, membershipApplicationID) != 0 &&
                jdbc.update(DELETE_MembershipApplication, membershipApplicationID) != 0;
    }

    /**
     * 获取所有申请
     *
     * @return 所有申请的列表
     */
    @Override
    public List<MembershipApplication> findAll() {
        return jdbc.query(SELECT_MembershipApplication + " order by membershipApplicationID", new MembershipApplicationRowMapper());
    }

    /**
     * 获取社团所有申请
     *
     * @param club 社团
     * @return 所有申请的列表
     */
    @Override
    public List<MembershipApplication> findAllByClub(Club club) {
        return jdbc.query(SELECT_MembershipApplication + " where requestedClubID=? order by membershipApplicationID", new MembershipApplicationRowMapper(),club.getClubID());
    }

    /**
     * 通过UserID查找用户申请
     *
     * @param userID 用户ID
     * @return 申请列表
     */
    @Override
    public List<MembershipApplication> findAllByUserID(int userID) {
        return jdbc.query("select ma.* from membershipApplication as ma, applicant_membershipApplication as am where am.userID=? and am.membershipApplicationID=ma.membershipApplicationID", new MembershipApplicationRowMapper(), userID);
    }

    static class MembershipApplicationRowMapper implements RowMapper<MembershipApplication> {

        public MembershipApplication mapRow(ResultSet rs, int i) throws SQLException {
            int membershipApplicationID = rs.getInt("membershipApplicationID");
            MembershipApplication ma = new MembershipApplication(
                    membershipApplicationID,
                    rs.getInt("requestedClubID"),
                    rs.getTimestamp("applicationTime"),
                    rs.getInt("status"),
                    rs.getString("description"));
            User u = jdbc.queryForObject("select _user.* from _user,applicant_membershipapplication as am where am.userID=_user.userID and membershipApplicationID=?",
                    new JdbcUserRepository.UserRowMapper(),membershipApplicationID);
            ma.setApplicant(u);
            return ma;
        }
    }
}
